﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using MarginTrading.Entity;
using MarginTrading.Common;
using System.Data.OracleClient;
using System.Data;

namespace MarginTrading.Services
{
    public class HolidayServices
    {
        private static Holiday GetHolidayFromDataReader(IDataReader dataReader)
        {
            Holiday retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new Holiday();

                DateTime day = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["DAY"].ToString(), out day))
                {
                    retVal.day = day;
                }
                retVal.description = dataReader["DESCRIPTION"].ToString();
            }
            return retVal;
        }

        public static Holiday GetHolidayByDay(DateTime day)
        {
            Holiday retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM HOLIDAY WHERE TO_CHAR(DAY, 'dd-mm-yyyy') = '" + day.ToString("dd-MM-yyyy") + "'";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetHolidayFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static HolidayCollection GetHolidayCollection()
        {
            HolidayCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM HOLIDAY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new HolidayCollection();
                    while (dataReader.Read())
                    {
                        Holiday item = GetHolidayFromDataReader(dataReader);
                        if (item != null)
                        {
                            retVal.Add(item);
                        }
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetHolidayCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT * FROM HOLIDAY";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static void Add(Holiday Holiday)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "INSERT INTO HOLIDAY(DAY, DESCRIPTION) VALUES(:v1, :v2)";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = Holiday.day;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = Holiday.description;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(DateTime day)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "DELETE FROM HOLIDAY WHERE TO_CHAR(DAY, 'dd-mm-yyyy') = '" + day.ToString("dd-MM-yyyy") + "'";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(Holiday Holiday)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = Constants.DB_CONNECTION_STRING;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "UPDATE HOLIDAY SET " +
                                  "DESCRIPTION = :v2 WHERE DAY = :v1";
                cmd.Parameters.Add(":v1", OracleType.DateTime).Value = Holiday.day;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = Holiday.description;

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
